/*
 * 作者：姚俊
 * 功能：学生竞赛获奖的service
 * 日期：2015-07-01
 */
package admin.service.fruits;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import beanDao.MapToBean;

import teacher.bean.Stuaward;
import teacher.bean.Teacher;
import database.dao.MysqlCrud;

public class StudentAwardService {
	
	
	/**
	 * @category 根据名称对查询所有竞赛获奖进行分页
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @return papers List<Paper> 返回查询到的论文列表
	 * 
	 */
	public List<Stuaward> getSegregateStuAwardByAll(Stuaward stuAward,int pageNow,int pageSize) {
		
		String sql="select * from stuaward ";
		int j=0;
		String sql2=" stuawaId LIMIT "+(pageNow-1)+" ,"+pageSize+" ";
		List<Stuaward> stuAwards=new ArrayList<Stuaward>();
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();

		if(stuAward!=null)
		{
			if(stuAward.getStuId()!=null)
			{
				sql1.add(" stuId=? ");
				paras.add(String.valueOf(stuAward.getStuId()));
			}
			if(stuAward.getAwaname()!=null)
			{
				sql1.add(" awaname like ? ");
				paras.add("%"+String.valueOf(stuAward.getAwaname())+"%");
			}
			if(stuAward.getAwakind()!=null)
			{
				sql1.add(" awakind=? ");
				paras.add(String.valueOf(stuAward.getAwakind()));
			}
			if(stuAward.getAwalevel()!=null)
			{
				sql1.add(" awalevel=? ");
				paras.add(String.valueOf(stuAward.getAwalevel()));
			}
			if(stuAward.getDepartment()!=null)
			{
				sql1.add(" department like ? ");
				paras.add("%"+String.valueOf(stuAward.getDepartment())+"%");
			}
			
		}
		
		for(int i=0;i<sql1.size();i++)
		{
			if(i==0)
				sql+="where"+sql1.get(i);
			else
				sql+="and"+sql1.get(i);
		}
		
		//加入最后一个,该字符是用于分页
		if(sql1.size()==0)
			sql+=sql2;
		else
			sql+="and"+sql2;
		
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(j<lists.size()){
			Map map=lists.get(j);
			stuAwards.add(new MapToBean().MapToStuaward(map));
			j++;
		}
		return stuAwards;
	}
	
	
	/**
	 * @category 根据名称对查询所有竞赛获奖进行分页,按照方向
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @return papers List<Paper> 返回查询到的论文列表
	 * 
	 */
	public List<Stuaward> getSegregateStuAwardByDirection(Stuaward stuAward,Teacher teacher,int pageNow,int pageSize) {
		
		String sql="select * from stuaward as award,teacher as tea,students as stu where tea.teaId=stu.teaId and stu.stuId=award.stuId and tea.direction=? ";
		int j=0;
		String sql2=" stuawaId LIMIT "+(pageNow-1)+" ,"+pageSize+" ";
		List<Stuaward> stuAwards=new ArrayList<Stuaward>();
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		paras.add(teacher.getDirection().toString());
		
		if(stuAward!=null)
		{
			if(stuAward.getStuId()!=null)
			{
				sql1.add(" stuId=? ");
				paras.add(String.valueOf(stuAward.getStuId()));
			}
			if(stuAward.getAwaname()!=null)
			{
				sql1.add(" awaname like ? ");
				paras.add("%"+String.valueOf(stuAward.getAwaname())+"%");
			}
			if(stuAward.getAwakind()!=null)
			{
				sql1.add(" awakind=? ");
				paras.add(String.valueOf(stuAward.getAwakind()));
			}
			if(stuAward.getAwalevel()!=null)
			{
				sql1.add(" awalevel=? ");
				paras.add(String.valueOf(stuAward.getAwalevel()));
			}
			if(stuAward.getDepartment()!=null)
			{
				sql1.add(" department like ? ");
				paras.add("%"+String.valueOf(stuAward.getDepartment())+"%");
			}
			
		}
		
		for(int i=0;i<sql1.size();i++)
		{
				sql+="and"+sql1.get(i);
		}
		
		//加入最后一个,该字符是用于分页
		sql+="and"+sql2;
		
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		//System.out.println(list.size());
		while(j<lists.size()){
			Map map=lists.get(j);
			stuAwards.add(new MapToBean().MapToStuaward(map));
			j++;
		}
		return stuAwards;
	}
	
	/**
	 * @category 根据不同条件对查询所有科研项目的数量
	 * 
	 */
	public int getCountStuAwardByAll(Stuaward stuAward) {
		
		String sql="select count(*) from stuaward ";
		int count=0;
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		
		if(stuAward!=null)
		{
			if(stuAward.getStuId()!=null)
			{
				sql1.add(" stuId=? ");
				paras.add(String.valueOf(stuAward.getStuId()));
			}
			if(stuAward.getAwaname()!=null)
			{
				sql1.add(" awaname like ? ");
				paras.add("%"+String.valueOf(stuAward.getAwaname())+"%");
			}
			if(stuAward.getAwakind()!=null)
			{
				sql1.add(" awakind=? ");
				paras.add(String.valueOf(stuAward.getAwakind()));
			}
			if(stuAward.getAwalevel()!=null)
			{
				sql1.add(" awalevel=? ");
				paras.add(String.valueOf(stuAward.getAwalevel()));
			}
			if(stuAward.getDepartment()!=null)
			{
				sql1.add(" department like ? ");
				paras.add("%"+String.valueOf(stuAward.getDepartment())+"%");
			}
			
		}
		
		for(int i=0;i<sql1.size();i++)
		{
			if(i==0)
				sql+="where"+sql1.get(i);
			else
				sql+="and"+sql1.get(i);
		}
		
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		Map map=lists.get(0);
		count=Integer.parseInt(map.get(0).toString());
		return count;
	}
	
	
	/**
	 * @category 根据不同条件对特定方向查询所有科研项目的数量
	 * 
	 */
	public int getCountStuAwardByDirection(Stuaward stuAward,Teacher teacher) {
		
		String sql="select count(*) from stuaward as award,teacher as tea,students as stu where tea.teaId=stu.teaId and stu.stuId=award.stuId and tea.direction=? ";
		int count=0;
		List<String> sql1=new ArrayList<String>();
		List<String> paras=new ArrayList<String>();
		paras.add(String.valueOf(teacher.getDirection()));
		
		if(stuAward!=null)
		{
			if(stuAward.getStuId()!=null)
			{
				sql1.add(" stuId=? ");
				paras.add(String.valueOf(stuAward.getStuId()));
			}
			if(stuAward.getAwaname()!=null)
			{
				sql1.add(" awaname like ? ");
				paras.add("%"+String.valueOf(stuAward.getAwaname())+"%");
			}
			if(stuAward.getAwakind()!=null)
			{
				sql1.add(" awakind=? ");
				paras.add(String.valueOf(stuAward.getAwakind()));
			}
			if(stuAward.getAwalevel()!=null)
			{
				sql1.add(" awalevel=? ");
				paras.add(String.valueOf(stuAward.getAwalevel()));
			}
			if(stuAward.getDepartment()!=null)
			{
				sql1.add(" department like ? ");
				paras.add("%"+String.valueOf(stuAward.getDepartment())+"%");
			}
			
		}
		
		for(int i=0;i<sql1.size();i++)
		{
				sql+="and"+sql1.get(i);
		}
		
		String[] parameter=new String[paras.size()];
		for(int i=0;i<paras.size();i++)
		{
			parameter[i]=paras.get(i);
		}
		
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		Map map=lists.get(0);
		count=Integer.parseInt(map.get(0).toString());
		return count;
	}

}
